package com.gadgets.busi.bm03.controller;

import com.gadgets.busi.bm03.common.Bm03Constant;
import com.gadgets.busi.bm03.common.Bm03ErrorCodeConstant;
import com.gadgets.busi.bm03.common.dto.ExcelRowProcessDTO;
import com.gadgets.busi.bm03.common.dto.SqlGeneratorDTO;
import com.gadgets.busi.bm03.common.dto.sql.ColumnInfoDTO;
import com.gadgets.busi.bm03.common.dto.sql.IndexInfoDTO;
import com.gadgets.busi.bm03.process.impl.sql.AbstractRowSqlProcess;
import com.gadgets.framework.common.dto.HttpBaseResp;
import com.gadgets.framework.common.exception.GadgetsServiceException;
import com.gadgets.framework.common.util.DateUtils;
import com.gadgets.framework.common.util.ExcelImportUtil;
import com.gadgets.framework.common.util.FileOperUtil;
import com.gadgets.framework.common.util.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping(method = {org.springframework.web.bind.annotation.RequestMethod.POST,org.springframework.web.bind.annotation.RequestMethod.GET},value = "sqlGenerator/")
public class MysqlSqlGeneratorController {

    protected final Logger logger = LoggerFactory.getLogger(this.getClass());

    private static final List<AbstractRowSqlProcess> processList = new ArrayList<>();

    /**
     * 文件上传基础路径
     */
    @Value("${spring.uploadPath}")
    private String uploadPath;
    @Value("${spring.downloadPath}")
    private String downloadPath;


    /**
     * SQL生成器
     * @param request           网络请求信息
     * @param response          网络应答信息
     * @param generatorDTO      SQL生成要素信息
     * @return                  受理结果
     */
    @RequestMapping(method = {org.springframework.web.bind.annotation.RequestMethod.POST,org.springframework.web.bind.annotation.RequestMethod.GET},value = "mysqlBuild.do")
    @SuppressWarnings("rawtypes")
    public HttpBaseResp sqlGenerator(HttpServletRequest request, HttpServletResponse response, SqlGeneratorDTO generatorDTO){
        File file = new File(uploadPath+"/"+generatorDTO.getFileKey()+"."+generatorDTO.getFileSuffix());
        if(file.exists()){
            Workbook workbook = ExcelImportUtil.getExcelFile(file,generatorDTO.getFileSuffix());
            int sheetNum = 1;
            int totalSheetNum = workbook.getNumberOfSheets();
            for (Sheet sheet : workbook) {
                if(sheetNum>2 && sheetNum<=totalSheetNum-2){
                    ExcelRowProcessDTO sheetesult = readSqlExcel(sheet);
                    // 输出SQL
                    writeDdlSql(sheetesult, generatorDTO.getFileKey());
                    // 输出Mapper.xml
                    writeMapperFile(sheetesult, generatorDTO.getFileKey());
                    // 输出Pojo类定义
                    writeDbPojo(sheetesult, generatorDTO.getFileKey());
                }
                sheetNum++;
            }
            return new HttpBaseResp();
        }else{
            throw new GadgetsServiceException(Bm03ErrorCodeConstant.SQL_DATA_FAIL_NOT_EXIST,generatorDTO.getFileKey());
        }
    }

    /**
     * 构建Mybatis映射文件
     * @param excelRowProcessDTO    表信息
     * @param basePath              基础路径
     */
    protected void writeMapperFile(ExcelRowProcessDTO excelRowProcessDTO, String basePath){
        String className = (String) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.CLASS_NAME);
        List<ColumnInfoDTO> columnInfoDTOList = (List<ColumnInfoDTO>) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.COLUMN);
        String fileMapperPath = downloadPath+"/"+ basePath + "/" + className +"Mapper.xml";
        StringBuilder sqlBuilder = new StringBuilder();
        // 文件头
        sqlBuilder.append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n")
                .append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\"\n")
                .append("\t\"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n");
        // 初始标签
        sqlBuilder.append("<mapper namespace=\"\">\n");
        // 结果集标签
        sqlBuilder.append("\t<resultMap id=\"").append(className).append("Map\" type=\"\">\n");
        for (ColumnInfoDTO columnInfoDTO : columnInfoDTOList) {
            String tagName = "result";
            if("id".equals(columnInfoDTO.getJavaParamName())){
                tagName = "id";
            }
            // 属性标签
            sqlBuilder.append("\t\t<").append(tagName).append(" property=\"").append(columnInfoDTO.getJavaParamName()).append("\"")
                    .append(" column=\"").append(columnInfoDTO.getColumnName()).append("\"/>\n");
        }
        sqlBuilder.append("\t</resultMap>\n");
        // 结束标签
        sqlBuilder.append("</mapper>\n");
        FileOperUtil.writeTxtTotFile(fileMapperPath,sqlBuilder.toString());
    }

    /**
     * 创建POJO文件
     * @param excelRowProcessDTO    表信息
     * @param basePath              基础路径
     */
    @SuppressWarnings("unchecked")
    protected void writeDbPojo(ExcelRowProcessDTO excelRowProcessDTO, String basePath){
        String className = (String) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.CLASS_NAME);
        String fileDdlPath = downloadPath+"/"+ basePath + "/" + className +".java";
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("public class ").append(className).append(" implements Serializable{");
        // 创建序列化属性
        sqlBuilder.append("\n");
        sqlBuilder.append("\tprivate static final long serialVersionUID = 1L;\n");
        List<ColumnInfoDTO> columnInfoDTOList = (List<ColumnInfoDTO>) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.COLUMN);
        // 创建基础属性
        for (ColumnInfoDTO columnInfoDTO : columnInfoDTOList) {
            if(Bm03Constant.REVERSE_CODE.NO_GENERATOR_PARAM_NAME.equals(columnInfoDTO.getJavaParamName())){
                continue;
            }
            // JavaDoc
            sqlBuilder.append("\t/**\n");
            sqlBuilder.append("\t*\t").append(columnInfoDTO.getColumnComment()).append("\n");
            sqlBuilder.append("\t*/\n");
            // 属性定义
            sqlBuilder.append("\tprivate ").append(columnInfoDTO.getJavaParamType()).append(" ").append(columnInfoDTO.getJavaParamName());
            if(!StringUtils.isBlank(columnInfoDTO.getDefaultValue())){
                sqlBuilder.append(" = ");
                // 如果有默认值，Java类就直接做属性值默认
                sqlBuilder.append(columnInfoDTO.getDefaultValue());
                if("Long".equals(columnInfoDTO.getColumnDataType())){
                    // Long型的属性，尾部增加一个L
                    sqlBuilder.append("L");
                }else if("Double".equals(columnInfoDTO.getColumnDataType())){
                    // Double型的属性，尾部增加一个D
                    sqlBuilder.append("D");
                }
            }
            sqlBuilder.append(";\n");
        }
        sqlBuilder.append("}");
        FileOperUtil.writeTxtTotFile(fileDdlPath,sqlBuilder.toString());
    }

    /**
     * 按表维度生成SQL文件
     * @param excelRowProcessDTO    表信息
     */
    protected void writeDdlSql(ExcelRowProcessDTO excelRowProcessDTO,String basePath){
        String fileBasePath = downloadPath+"/"+basePath;
        File basePathFileBook = new File(fileBasePath);
        if(!basePathFileBook.exists() && !basePathFileBook.mkdir()){
            // 如果文件路径不存在，且创建路径失败，抛出异常
            throw new GadgetsServiceException("SQL文件输出路径创建失败");
        }
        String fileDdlPath = fileBasePath+"/"+ DateUtils.getData8bit()+"-ddl.sql";
        StringBuilder sqlBuilder = new StringBuilder();
        String tableName = (String) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.TABLE_NAME);
        // 生成建表语句
        List<IndexInfoDTO> indexInfoDTOList = buildMysqlTableCreate(excelRowProcessDTO, sqlBuilder, tableName);
        buildMysqlIndexAndUniqueCreate(sqlBuilder, tableName, indexInfoDTOList);
        sqlBuilder.append("\n\n\n");
        FileOperUtil.writeTxtTotFile(fileDdlPath,sqlBuilder.toString());
    }

    protected void buildMysqlIndexAndUniqueCreate(StringBuilder sqlBuilder, String tableName, List<IndexInfoDTO> indexInfoDTOList) {
        // INDEX 和 UNIQUE 建立语句
        for (IndexInfoDTO indexInfoDTO : indexInfoDTOList) {
            // pk/unique has build on column, not need to build again
            if(Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_PK.equals(indexInfoDTO.getIndexType())||
                    Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_PK_AUTO.equals(indexInfoDTO.getIndexType())||
                    Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_NOT_NULL.equals(indexInfoDTO.getIndexType())
            ){
                continue;
            }
            if(Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_UNIQ.equals(indexInfoDTO.getIndexType())){
                // unique 约束生成
                buildMysqlUnique(sqlBuilder, tableName, indexInfoDTO);
            }else {
                // index、unique index 约束
                buildMysqlIndex(sqlBuilder, tableName, indexInfoDTO);
            }
        }
    }

    protected void buildMysqlIndex(StringBuilder sqlBuilder, String tableName, IndexInfoDTO indexInfoDTO) {
        sqlBuilder.append("drop index ").append(indexInfoDTO.getIndexName()).append(" on ").append(tableName).append(";\n");
        sqlBuilder.append("create ").append(indexInfoDTO.getIndexType()).append(" ").append(indexInfoDTO.getIndexName()).append(" on ").append(tableName).append("(");
        for (String column : indexInfoDTO.getIndexColumnName()) {
            sqlBuilder.append(column).append(",");
        }
        sqlBuilder.setLength(sqlBuilder.length()-1);
        sqlBuilder.append(");\n\n");
    }

    protected void buildMysqlUnique(StringBuilder sqlBuilder, String tableName, IndexInfoDTO indexInfoDTO) {
        sqlBuilder.append("alter table ").append(tableName).append(" add unique(");
        for (String column : indexInfoDTO.getIndexColumnName()) {
            sqlBuilder.append(column).append(",");
        }
        sqlBuilder.setLength(sqlBuilder.length()-1);
        sqlBuilder.append(");\n\n");
    }

    @SuppressWarnings("unchecked")
    protected List<IndexInfoDTO> buildMysqlTableCreate(ExcelRowProcessDTO excelRowProcessDTO, StringBuilder sqlBuilder, String tableName) {
        String tableCommentName = (String) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.TABLE_COMMENT);
        sqlBuilder.append("-- ").append(tableCommentName).append("\n");
        // DROP 语句
        sqlBuilder.append("drop table ").append(tableName).append(";\n");
        // CREATE 语句
        sqlBuilder.append("create table ").append(tableName).append("(\n");
        List<ColumnInfoDTO> columnInfoDTOList = (List<ColumnInfoDTO>) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.COLUMN);
        List<IndexInfoDTO> indexInfoDTOList = (List<IndexInfoDTO>) excelRowProcessDTO.getParamMap().get(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.INDEX);
        int i = 0;
        IndexInfoDTO pkIndex = null;
        for (ColumnInfoDTO columnInfoDTO : columnInfoDTOList) {
            i = buildMysqlColumnInfo(sqlBuilder, indexInfoDTOList, i, columnInfoDTO);
        }
        sqlBuilder.deleteCharAt(sqlBuilder.lastIndexOf(","));
        sqlBuilder.append(")DEFAULT CHARSET = 'UTF8';\n\n");
        return indexInfoDTOList;
    }

    protected int buildMysqlColumnInfo(StringBuilder sqlBuilder, List<IndexInfoDTO> indexInfoDTOList, int i, ColumnInfoDTO columnInfoDTO) {
        String columnName = columnInfoDTO.getColumnName();
        // 循环构建表字段
        sqlBuilder.append("\t").append(columnInfoDTO.getColumnName()).append(" ").append(columnInfoDTO.getColumnDataType());
        // 默认值
        if(!StringUtils.isBlank(columnInfoDTO.getDefaultValue())){
            sqlBuilder.append(" DEFAULT ").append(columnInfoDTO.getDefaultValue().replace("\"", "'"));
        }
        // 字段自增约束说明
        boolean autoIncreament = false;
        // 判断是否存在主键和非空约束
        for (IndexInfoDTO indexInfoDTO : indexInfoDTOList) {
            boolean flag = false;
            // 组合索引后面单独处理，此处只处理单独的索引
            if(indexInfoDTO.getIndexColumnName().length==1){
                flag = columnName.equals(indexInfoDTO.getIndexColumnName()[0]);
            }
            // 如果flag为false就不需要执行后面的拼接逻辑和判断
            if(!flag){
                continue;
            }
            // 主键和主键自增
            if((Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_PK.equals(indexInfoDTO.getIndexType())
                    || (autoIncreament = Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_PK_AUTO.equals(indexInfoDTO.getIndexType())))
            ){
                sqlBuilder.append(" ").append(Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_PK);
                if(autoIncreament){
                    sqlBuilder.append(" AUTO_INCREMENT");
                }
            }
            // 非空约束说明
            if(Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_NOT_NULL.equals(indexInfoDTO.getIndexType())){
                sqlBuilder.append(" ").append(Bm03Constant.SQL_GEN_INDEX_TYPE.INDEX_NOT_NULL).append(" ");
            }
        }
        // 默认值
        if(!StringUtils.isBlank(columnInfoDTO.getDefaultValue())){
            sqlBuilder.append(" DEFAULT ").append(columnInfoDTO.getDefaultValue().replace("\"", "'"));
        }
        // 字段注释
        sqlBuilder.append(" COMMENT '").append(columnInfoDTO.getColumnComment()).append("'");
        sqlBuilder.append(",\n");
        i++;
        return i;
    }

    /**
     * Excel-Sheet内容读取
     * @param sheet Sheet信息
     * @return      读取结果
     */
    protected ExcelRowProcessDTO readSqlExcel(Sheet sheet) {
        ExcelRowProcessDTO processDTO = new ExcelRowProcessDTO();
        processDTO.setTaskType(Bm03Constant.EXCEL_PROCESS_TASK_TYPE.SQL_GENERATOR);
        processDTO.getParamMap().put(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.INDEX,new ArrayList<IndexInfoDTO>());
        processDTO.getParamMap().put(Bm03Constant.SQL_GEN_TASK_PARAM_KEY.COLUMN,new ArrayList<ColumnInfoDTO>());
        String sqlName = sheet.getSheetName();
        // 遍历每行，获取信息
        int rowNum = 1;
        for (Row row : sheet) {
            processDTO.setRowNum(rowNum);
            logger.info("当前读取{}Sheet的第{}行",sqlName,rowNum);
            boolean excuteFlag = false;
            for (Cell cell : row) {
                if(!StringUtils.isBlank(cell.getStringCellValue())){
                    excuteFlag = true;
                    break;
                }
            }
            if(excuteFlag) {
                for (AbstractRowSqlProcess process : processList) {
                    process.excute(row, processDTO);
                }
            }
            rowNum++;
        }
        return processDTO;
    }

    /**
     * 注册处理器列表
     * @param process   注册器对象
     */
    public static void addProcess(AbstractRowSqlProcess process){
        processList.add(process);
    }

}
